﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OneSoft.EntityCore;
using OneSoft.DataAccess;
using BySoft.Server.Common.Enum;

using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;
using BySoft.Server.Consts;

namespace BySoft.Server.DataAccess
{
    public partial class SysModuleDA
    {
        public static DataTable GetSysModuleList()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "select * from modulelist_view ";

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }


  
        public static DataTable GetTopModuleListByAdmin()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " select " + SysModuleTable.C_MODULEID;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_ORDERNO;
            sSql += " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_PAGETYPE + "='" + BaseEnum.PageType.T.ToString() + "'";
            //sSql += " and " + SysModuleTable.C_MODULEID + "<>'0001'";
            sSql += " order by " + SysModuleTable.C_ORDERNO;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }

        public static DataTable GetHomeTopModuleList(string sParentModuleID)
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " select " + SysModuleTable.C_MODULEID;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_ORDERNO + "," + SysModuleTable.C_MODULEPRIMARYID + "," + SysModuleTable.C_FUNCTIONURL + "," + SysModuleTable.C_PARENTMODULEID;
            sSql += " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_PAGETYPE + "='" + BaseEnum.PageType.F.ToString() + "'";
            //sSql += " and  " + SysModuleTable.C_CANVIEW + "= '1' " ;
            sSql += " and  " + SysModuleTable.C_STATUS + "= '1' ";
            sSql += " and  " + SysModuleTable.C_PARENTMODULEID + "=  '" + sParentModuleID.Trim() + "'";
            sSql += " order by " + SysModuleTable.C_ORDERNO;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }

        //get pageType为S的
        public static DataTable MyGetTopModuleListByAdmin()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " select " + SysModuleTable.C_MODULEID;
            sSql += "," + SysModuleTable.C_MODULEPRIMARYID + "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_ORDERNO;
            sSql += " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_PAGETYPE + "='" + BaseEnum.PageType.S.ToString() + "' and status='1'";
            sSql += " order by " + SysModuleTable.C_ORDERNO;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }

        //get pageType为F的
        public static DataTable MyGetSedMenuListByAdmin()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "select  " + SysModuleTable.C_MODULEID + "," + SysModuleTable.C_FUNCTIONURL;
            sSql += "," + SysModuleTable.C_MODULEPRIMARYID + "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_PARENTMODULEID;
            sSql += "," + SysModuleTable.C_ORDERNO + " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_PAGETYPE + " ='" + BaseEnum.PageType.F.ToString() + "' and status='1'";
            //modify 2011/10/26 解决菜单显示不够问题，临时修改
            sSql += " AND "+SysModuleTable.C_DATARIGHTS +" IS NULL ";
            sSql += " order by " + SysModuleTable.C_ORDERNO;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }

   
        public static DataTable GetSedMenuListByAdmin()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "select  " + SysModuleTable.C_MODULEID + "," + SysModuleTable.C_FUNCTIONURL;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_PARENTMODULEID;
            sSql += "," + SysModuleTable.C_ORDERNO + " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_PAGETYPE + " in('" + BaseEnum.PageType.S.ToString() + "',";
            sSql += "'" + BaseEnum.PageType.F.ToString() + "') ";
            //sSql += " and " + SysModuleTable.C_PARENTMODULEID + "<>'0001'";
            sSql += " order by " + SysModuleTable.C_ORDERNO;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }

        public static DataTable GetTopModuleListByUser(string userId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "select * from ( ";

            if (!string.IsNullOrEmpty(userId))
            {
                sSql += " SELECT  * FROM fn_FindTopModuleListByUser(@userId) ";
                parameters.AddWithValue("@userId", userId);
            }
            else
            {
                sSql += " SELECT  * FROM fn_FindTopModuleList() ";
            }
            sSql += @" UNION
SELECT a.ParentModuleID,a.ModuleID,a.ModuleName,a.PageType, a.OrderNo,
       a.ModulePrimaryID
  FROM SYS_Module a WHERE a.ModuleID='FS01'";
            sSql += " ) T";
            sSql += " order by " + SysModuleTable.C_ORDERNO;
            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

        public static DataTable MyGetTopModuleListByUser(string userId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "select * from ( ";

            if (!string.IsNullOrEmpty(userId))
            {
                sSql += " SELECT  * FROM fn_FindTopModuleListByUser(@userId) ";
                parameters.AddWithValue("@userId", userId);
            }
            else
            {
                sSql += " SELECT  * FROM fn_FindTopModuleList() ";
            }
            sSql += " union ";
            sSql += " select PARENTMODULEID ," + SysModuleTable.C_MODULEID;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_ORDERNO;
            sSql += " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_MODULEID + "='ST01') T";
            sSql += " order by " + SysModuleTable.C_ORDERNO;
            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }


        public static DataTable GetSedMenuListByUser(string userId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            //string sSql = "select * from ( select * from ( select distinct " + SysModuleTable.C_MODULEID + "," + SysModuleTable.C_FUNCTIONURL;
            //sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_PARENTMODULEID;
            //sSql += "," + SysModuleTable.C_ORDERNO + " from " + SysModuleTable.C_TABLENAME;
            //sSql += " start with " + SysModuleTable.C_MODULEID + " in (";
            //sSql += " select " + SysModuleTable.C_MODULEID + " from userrights_view ";
            ////sSql += " ) connect by prior " + SysModuleTable.C_PARENTMODULEID;
            //sSql += " where 1=1 ";
            //if (!string.IsNullOrEmpty(userId))
            //{
            //    sSql += " and userId = :userId ";
            //    parameters.AddWithValue(":userId", userId);
            //}
            //sSql += " ) connect by prior " + SysModuleTable.C_PARENTMODULEID;
            //sSql += " = " + SysModuleTable.C_MODULEID + ")";
            //sSql += " where " + SysModuleTable.C_PAGETYPE + " in('" + BaseEnum.PageType.S.ToString() + "',";
            //sSql += "'" + BaseEnum.PageType.F.ToString() + "') "; 
            string sSql = "select * from ( ";
            if (!string.IsNullOrEmpty(userId))
            {
                //sSql += " SELECT  * FROM fn_FindSedMenuListByUser(@userId)
                sSql += " SELECT  * FROM fn_FindSedMenuListByUser(@userId) where DataRights is NULL ";


                parameters.AddWithValue("@userId", userId);
            }
            else
            {
                // sSql += " SELECT  * FROM fn_FindSedMenuList()
                sSql += " SELECT  * FROM fn_FindSedMenuList() where DataRights is NULL ";


            }
            sSql += " union ";
            sSql += " select distinct " + SysModuleTable.C_MODULEID + "," + SysModuleTable.C_FUNCTIONURL;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_PARENTMODULEID;
            sSql += "," + SysModuleTable.C_ORDERNO + "," + SysModuleTable.C_DATARIGHTS + "," + SysModuleTable.C_MODULEPRIMARYID + " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_MODULEID + "='ST011' and status='1' ";
            sSql += " union ";
            sSql += " select distinct " + SysModuleTable.C_MODULEID + "," + SysModuleTable.C_FUNCTIONURL;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_PARENTMODULEID;
            sSql += "," + SysModuleTable.C_ORDERNO + "," + SysModuleTable.C_DATARIGHTS + "," + SysModuleTable.C_MODULEPRIMARYID + " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_MODULEID + "='ST012' and status='1') T";
            sSql += " order by " + SysModuleTable.C_ORDERNO;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

       

        /// <summary>
        /// 根据地址查询顶级菜单id
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public static object GetTopModuleIdByUrl(string url)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " SELECT moduleid";
            sSql += " FROM (SELECT  moduleid, pagetype";
            sSql += " FROM sys_module";
            sSql += " START WITH moduleid =";
            sSql += " (SELECT moduleid";
            sSql += " FROM sys_module";
            sSql += " WHERE functionurl =@url";
            sSql += " AND pagetype != 'F')";
            sSql += " CONNECT BY PRIOR parentmoduleid = moduleid)";
            sSql += " WHERE pagetype = 'T'";
            parameters.AddWithValue("@url", url);

            return DataAccessCommon.SelectSingleObject(sSql, parameters);
            #endregion
        }

        /// <summary>
        /// 根据URL地址得到二级模块信息
        /// </summary>
        /// <param name="url"></param>
        /// <returns></returns>
        public static DataTable GetLeftModuleInfoByUrl(string url)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string strSql = "SELECT * FROM sys_module WHERE pagetype='F' AND Lower(functionurl)=@url ";
            parameters.AddWithValue("@url", url.ToLower());
            return DataAccessCommon.Select(strSql, parameters).Tables[0];
            #endregion
        }


        /// <summary>
        /// 得到二级模块List
        /// </summary>
        /// <returns></returns>
        public static DataTable GetLeftModuleList()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string strSql = "SELECT ModuleID,ModuleName FROM sys_module WHERE pagetype='F' ";

            return DataAccessCommon.Select(strSql, parameters).Tables[0];
        }

        /// <summary>
        /// 判断当前模块是否有子模块
        /// </summary>
        /// <param name="moduleID"></param>
        /// <returns></returns>
        public static bool IsExistChildModule(string moduleID)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string strSql = "SELECT COUNT(*) FROM sys_module WHERE ParentModuleID=@ParentModuleID ";
            parameters.AddWithValue("@ParentModuleID", moduleID);
            object objCount = DataAccessCommon.SelectSingleObject(strSql, parameters);
            if (Convert.ToInt32(objCount) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
            #endregion
        }

        public static string GetNextModuleID(string strParentID)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    string strcmd = @"SELECT MAX(a.ModuleID) FROM SYS_Module a WHERE a.ParentModuleID=@ParentModuleID";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@ParentModuleID", strParentID);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null && !string.IsNullOrEmpty(obj.ToString().Trim()))
                    {
                        string str = obj.ToString().Trim();
                        str = str.Substring(str.Length - 2, 2);
                        int itep = Convert.ToInt32(str) + 1;
                        return strParentID + itep.ToString().PadLeft(2, '0');
                    }
                    else
                    {
                        return strParentID + "01";
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        public static int GetNextOrderID(string strParentID)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    string strcmd = @"SELECT MAX(a.OrderNo) FROM SYS_Module a WHERE a.ParentModuleID=@ParentModuleID OR A.ModuleID=@ParentModuleID";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@ParentModuleID", strParentID);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null && !string.IsNullOrEmpty(obj.ToString().Trim()))
                    {
                        string str = obj.ToString().Trim();
                        return Convert.ToInt32(str) + 1;
                    }
                    else
                    {
                        return 0;
                    }
                }
                catch
                {
                    throw;
                }
            }
        }

        /// <summary>
        ///插入模块
        /// </summary>
        /// <param name="enModule"></param>
        /// <returns></returns>
        public static void InsertSysModule(SysModuleEntity enModule, DataAccessBroker broker)
        {
            //if (blSequ)
            //{
            //    string strSql = "SELECT SYSMODULE_SEQU.NEXTVAL FROM DUAL";
            //    string strID = DataAccessCommon.SelectSingleObject(strSql).ToString();
            //    enModule.Moduleid = enModule.Moduleid + strID;
            //}
            DataAccessCommon.Insert(enModule, broker);




        }
        /// <summary>
        /// 删除模块数据，首先删除权限数据
        /// </summary>
        /// <param name="moduleID"></param>
        /// <param name="broker"></param>
        public static void DeleteSysModule(string moduleID, DataAccessBroker broker)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            parameters.AddWithValue("@ModuleID", moduleID);
            string strDelRights = "DELETE FROM SYS_RoleRights WHERE ModuleID =@ModuleID";
            string strDelModule = "DELETE FROM sys_module WHERE ModuleID =@ModuleID";
            broker.ExecuteSQL(strDelRights, parameters);
            broker.ExecuteSQL(strDelModule, parameters);
            #endregion
        }

        public static DataTable GetSystemManageModuleByUserID(string UserID)
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(" select * ");
            strSQL.Append(" from SYS_UserRole A ");
            strSQL.Append(" left join SYS_RoleRights B on A.RoleID=B.RoleID ");
            strSQL.Append(" left join SYS_Module C on B.ModuleID=C.ModuleID ");
            strSQL.Append(" where A.UserID=@UserID ");
            strSQL.Append(" and C.PageType='F' ");
            strSQL.Append(" and C.ParentModuleID='SYS01' ");
            parameters.AddWithValue("UserID", UserID);
            return DataAccessCommon.Select(strSQL.ToString(), parameters).Tables[0];
        }

        public static DataTable Select(string strtxtModuleID, string strtxtModuleName, string strtxtParentModuleID, string strchkCanView)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT * FROM dbo.ChildModuleAll() a
WHERE a.PAGETYPE='T' OR a.OrderNo IS NOT NULL";
                    if (!string.IsNullOrEmpty(strtxtModuleID))
                    {
                        strcmd += " AND a.ModuleID LIKE @ModuleID";
                        dpc.AddWithValue("@ModuleID", "%" + strtxtModuleID + "%");
                    }
                    if (!string.IsNullOrEmpty(strtxtModuleName))
                    {
                        strcmd += " AND a.MODULENAME LIKE @MODULENAME";
                        dpc.AddWithValue("@MODULENAME", "%" + strtxtModuleName + "%");
                    }
                    if (!string.IsNullOrEmpty(strtxtParentModuleID))
                    {
                        strcmd += " AND a.ParentModuleID = @ParentModuleID";
                        dpc.AddWithValue("@ParentModuleID", strtxtParentModuleID);
                    }
                    strcmd += " ORDER BY a.OrderNo";
                    
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable Select()
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT a.ModuleID,a.ModuleName 
FROM SYS_Module a 
WHERE a.PageType='F' AND A.ParentModuleID<>'SYS01' AND a.ParentModuleID<>'MD01'
AND NOT EXISTS(SELECT * FROM SYS_FormAudit aa WHERE aa.ModuleID=a.ModuleID)
ORDER BY a.ParentModuleID, a.OrderNo";

                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static bool IsExistsModuleID(string strModuleID)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT a.ModulePrimaryID FROM SYS_Module a 
WHERE a.[Status]='1' AND a.ModuleID=@ModuleID";
                    dpc.AddWithValue("@ModuleID", strModuleID);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null && !string.IsNullOrEmpty(obj.ToString().Trim()))
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectHomeData(string sType,string strCode ,string sIndex)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @" select  
                         ";
                    if (!string.IsNullOrEmpty(sIndex))
                    {
                        strcmd += " top 7  ";
                    }
                    strcmd += @" MainPageNewsId,Title,SUBSTRING(Title,0,11)+'...' as TitleName,[Source],CONVERT(varchar(12) , PublishDate,23 ) PublishDate,Content,NewsType,ViewNum,Source,OrderNo,CoverPic,Status
                            FROM BS_MainPageNews		
                        WHERE 1=1  and [Status]='1'  ";

                    if (!string.IsNullOrEmpty(strCode))
                    {
                        strcmd += " and MainPageNewsId=@MainPageNewsId ";
                        dpc.AddWithValue("@MainPageNewsId", strCode);
                    }
                    if (!string.IsNullOrEmpty(sType))
                    {
                        strcmd += " and NewsType=@NewsType ";
                        dpc.AddWithValue("@NewsType", sType);
                    }
                    strcmd += " order by  OrderNo desc";
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectHomeContent(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"      
  select top 7 a.ContentTypeId,a.ContentPublishId,a.Title,SUBSTRING(a.Title,0,17)+'...' as TitleName,a.[Source],CONVERT(varchar(12) , a.PublishDate,23 ) PublishDate from BS_ContentPublish a 
  left join SYS_ContentType b on b.ContentTypeId=a.ContentTypeId
  where b.ModulePrimaryID=@ModulePrimaryID and a.[Status]='1'
  order by a.PublishDate desc";
                    dpc.AddWithValue("@ModulePrimaryID", strCode);
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectLeftMenu()
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"      
  select * from SYS_ContentType
  where [Status]='1'
  order by OrderNo ";
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectRightList(string strCode, string sType)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"      
    select ContentPublishId,ContentTypeId,Title,Content,ViewNum,CONVERT(varchar(12) , PublishDate,23 ) PublishDate,Source,Status from BS_ContentPublish
  where [Status]='1' and 1=1";
                    if (sType == "1")
                    {
                        strcmd += " and  ContentTypeId=@ContentTypeId ";
                        dpc.AddWithValue("@ContentTypeId", strCode);
                    }
                    else 
                    {
                        strcmd += " and  ContentPublishId=@ContentPublishId ";
                        dpc.AddWithValue("@ContentPublishId", strCode);
                    }
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectContent(string strCode, string sType)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"  select Title,Content,ViewNum,PublishDate,[Source] ";
                    if (sType == "1")
                    {
                        strcmd += " from BS_ContentPublish  where ContentPublishId=@ContentPublishId";
                        dpc.AddWithValue("@ContentPublishId", strCode);
                    }
                    else
                    {
                        strcmd += " ,CoverPic from BS_MainPageNews where MainPageNewsId=@MainPageNewsId ";
                        dpc.AddWithValue("@MainPageNewsId", strCode);
                    }
                    strcmd += " and [Status]='1'  ";


                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectCultureModule(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"    select * from [SYS_Module] where [ParentModuleID] = (
  select [ParentModuleID] from [SYS_Module] where [ModulePrimaryID]=@ModulePrimaryID)
  and [Status]='1'  ";

                    dpc.AddWithValue("@ModulePrimaryID", strCode);
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }



        public static void UpdateViewNum(string strCode, int iViewNum, string sPageType, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"";
            if (!string.IsNullOrEmpty(strCode) && !string.IsNullOrEmpty(sPageType))
            {
                if (sPageType == "1")
                {
                    strcmd = @"UPDATE BS_ContentPublish SET ViewNum=" + iViewNum + " WHERE ContentPublishId=@ContentPublishId";
                    dpc.AddWithValue("@ContentPublishId", strCode);
                }
                else if (sPageType == "2")
                {
                    strcmd = @"UPDATE BS_MainPageNews SET ViewNum=" + iViewNum + " WHERE MainPageNewsId=@MainPageNewsId";
                    dpc.AddWithValue("@MainPageNewsId", strCode);
                }

            }

            broker.ExecuteSQL(strcmd, dpc);
        }

        public static DataTable SelectListModuleName(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"      
  select * from  SYS_Module
  where ModulePrimaryID=@ModulePrimaryID and [Status]='1'";
                    dpc.AddWithValue("@ModulePrimaryID", strCode);
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectModuleAllContent(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"      
  select  a.ContentTypeId,a.ContentPublishId,a.Title,SUBSTRING(a.Title,0,17)+'...' as TitleName,a.[Source],CONVERT(varchar(12) , a.PublishDate,23 ) PublishDate from BS_ContentPublish a 
  left join SYS_ContentType b on b.ContentTypeId=a.ContentTypeId
  where b.ModulePrimaryID=@ModulePrimaryID and a.[Status]='1'
  order by a.PublishDate desc";

                    dpc.AddWithValue("@ModulePrimaryID", strCode);
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }


        public static void UpdateStatus(string strCode, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE SYS_Module SET [Status]='0' WHERE ModulePrimaryID=@ModulePrimaryID;
UPDATE SYS_ContentType SET [Status]='0' WHERE ModulePrimaryID=@ModulePrimaryID";
            dpc.AddWithValue("@ModulePrimaryID", strCode);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static bool IsExistsChild(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT a.ModulePrimaryID FROM SYS_Module a 
WHERE a.[Status]='1' AND a.ParentModuleID=@ModuleID
AND a.ModuleID NOT LIKE @ModuleID+'_'";
                    dpc.AddWithValue("@ModuleID", strCode);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null && !string.IsNullOrEmpty(obj.ToString().Trim()))
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static string SelectPreSameParent(string strParent, string strOrderNo)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT  TOP 1 a.ModulePrimaryID FROM SYS_Module a 
WHERE a.[Status]='1' AND a.ParentModuleID=@ParentModuleID
AND a.OrderNo<@OrderNo
ORDER BY a.OrderNo DESC";
                    dpc.AddWithValue("@ParentModuleID", strParent);
                    dpc.AddWithValue("@OrderNo", strOrderNo);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null)
                    {
                        return obj.ToString().Trim();
                    }
                    else
                    {
                        return "";
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static string SelectNextSameParent(string strParent, string strOrderNo)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT  TOP 1 a.ModulePrimaryID FROM SYS_Module a 
WHERE a.[Status]='1' AND a.ParentModuleID=@ParentModuleID
AND a.OrderNo>@OrderNo
ORDER BY a.OrderNo";
                    dpc.AddWithValue("@ParentModuleID", strParent);
                    dpc.AddWithValue("@OrderNo", strOrderNo);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null)
                    {
                        return obj.ToString().Trim();
                    }
                    else
                    {
                        return "";
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static void SetOrderNo(string strCode, string strOtherCode, string strOrderNo, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE SYS_Module SET OrderNo = 
(SELECT TOP 1 aa.OrderNo FROM SYS_Module aa WHERE aa.ModulePrimaryID=@strOtherCode)
WHERE ModulePrimaryID=@strCode;
UPDATE SYS_Module SET OrderNo = @strOrderNo
WHERE ModulePrimaryID=@strOtherCode;
UPDATE SYS_Module SET SYS_Module.OrderNo =
(SELECT SUBSTRING(CONVERT(VARCHAR(50),AA.OrderNo),1,LEN(AA.OrderNo)-2)
   FROM SYS_Module AA WHERE AA.ModuleID=SYS_Module.ParentModuleID)+
(SELECT SUBSTRING(CONVERT(VARCHAR(50),AA.OrderNo),LEN(AA.OrderNo)-1,2)
   FROM SYS_Module AA WHERE AA.ModulePrimaryID=SYS_Module.ModulePrimaryID) 
WHERE [Status]='1' AND PageType='F';
UPDATE SYS_Module SET SYS_Module.OrderNo =
(SELECT SUBSTRING(CONVERT(VARCHAR(50),AA.OrderNo),1,LEN(AA.OrderNo)-2)
   FROM SYS_Module AA WHERE AA.ModuleID=SYS_Module.ParentModuleID)+
(SELECT SUBSTRING(CONVERT(VARCHAR(50),AA.OrderNo),LEN(AA.OrderNo)-1,2)
   FROM SYS_Module AA WHERE AA.ModulePrimaryID=SYS_Module.ModulePrimaryID) 
WHERE [Status]='1' AND PageType='F'";
            dpc.AddWithValue("@strOtherCode", strOtherCode);
            dpc.AddWithValue("@strCode", strCode);
            dpc.AddWithValue("@strOrderNo", strOrderNo);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static string SelectMaxModuleID(string strParent)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT MAX(a.ModuleID) FROM SYS_Module a 
WHERE a.[Status]='1' AND a.ParentModuleID=@ParentModuleID";
                    dpc.AddWithValue("@ParentModuleID", strParent);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null)
                    {
                        return obj.ToString().Trim();
                    }
                    else
                    {
                        return "";
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static int SelectOrderNo(string strParent, string strPageType, string strCanView)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = "";
                    if (strPageType.Equals("S"))
                    {
                        strcmd = @"SELECT ISNULL(MAX(a.OrderNo),0)+100 as OrderNo FROM SYS_Module a 
WHERE a.[Status]='1' AND a.PageType='S'
AND A.ModuleID<>'SYS01'
AND a.ParentModuleID=@ParentModuleID";
                    }
                    else if (strPageType.Equals("F"))
                    {
                        if (strCanView.Equals("0"))
                        {
                            strcmd = @"SELECT CASE WHEN MAX(a.OrderNo) IS NULL THEN 
(SELECT TOP 1 aa.OrderNo+1
	FROM SYS_Module aa 
	WHERE aa.ModuleID=@ParentModuleID AND aa.[Status]='1')
	ELSE MAX(a.OrderNo)+1 END AS OrderNo
FROM SYS_Module a 
WHERE a.[Status]='1' AND a.PageType='F'
AND a.ParentModuleID=@ParentModuleID";
                        }
                        else if (strCanView.Equals("1"))
                        {
                            strcmd = @"SELECT ISNULL(MAX(a.OrderNo),0)+1 as OrderNo
FROM SYS_Module a 
WHERE a.[Status]='1' AND a.PageType='F'
AND a.ParentModuleID=@ParentModuleID";
                        }
                    }

                    if (string.IsNullOrEmpty(strcmd))
                    {
                        return 0;
                    }
                    dpc.AddWithValue("@ParentModuleID", strParent);

                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj != null && !string.IsNullOrEmpty(obj.ToString().Trim()))
                    {
                        return Convert.ToInt32(obj.ToString().Trim());
                    }
                    else
                    {
                        return 0;
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

//        public static int SelectParentOrderNo(string strModuleID)
//        {
//            using (DataAccessBroker broker = DataAccessFactory.Instance())
//            {
//                try
//                {
//                    #region SQL Server
//                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
//                    string strcmd = @"SELECT a.OrderNo FROM SYS_Module a 
//WHERE a.[Status]='1' AND a.ModuleID=@ModuleID";
//                    dpc.AddWithValue("@ModuleID", strModuleID);

//                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
//                    if (obj != null && !string.IsNullOrEmpty(obj.ToString().Trim()))
//                    {
//                        return Convert.ToInt32(obj.ToString().Trim()) + 1;
//                    }
//                    else
//                    {
//                        return 0;
//                    }
//                    #endregion
//                }
//                catch
//                {
//                    throw;
//                }
//            }
//        }

        public static DataTable SelectHead(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT b.ModuleName AS PModuleName,a.* 
FROM SYS_Module a
INNER JOIN SYS_Module b ON b.ModuleID=a.ParentModuleID AND b.[Status]='1'
WHERE a.ModulePrimaryID=@ModulePrimaryID";
                    dpc.AddWithValue("@ModulePrimaryID", strCode);

                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static void DelChildCRUD(string strParent, DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"DELETE FROM SYS_Module WHERE ParentModuleID=@ParentModuleID
AND (PageType='R' OR PageType='C' OR PageType='U')
AND OrderNo IS NULL";
            dpc.AddWithValue("@ParentModuleID", strParent);
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static DataTable CheckUsed(string strCode)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    string strcmd = @"SELECT t.tablename
                                    FROM 
                                    (	                                    
                                        SELECT COUNT(A.ContentTypeId) AS NUM,'[信息类型]'+CONVERT(VARCHAR(50),A.ContentTypeId) AS tablename FROM SYS_ContentType A WHERE A.ModulePrimaryID=@strCode group by A.ContentTypeId
UNION
SELECT COUNT(A.RoleRightsID) AS NUM,'[权限表]'+CONVERT(VARCHAR(50),A.RoleRightsID) AS tablename FROM SYS_RoleRights A WHERE A.ModulePrimaryID=@strCode group by A.RoleRightsID
                                    ) t
                                    WHERE t.NUM>0";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@strCode", strCode);

                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];

                }
                catch
                {
                    throw;
                }
            }
        }


        public static DataTable SelectSearch()
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"  
  select '0' as ModulePrimaryID, '全文检索' as ModuleName, '0' as PageType
  union all
  select '1' as ModulePrimaryID, '图片新闻' as ModuleName, '3' as PageType
  union all
  select a.ModulePrimaryID,a.ModuleName ,'1' as PageType
  from [SYS_Module] a
  where PageType='F' and [Status]='1' and ParentModuleID='CS01' 
  union all
  select b.ModulePrimaryID,b.ModuleName ,'2' as PageType
  from [SYS_Module] b
  where PageType='F' and [Status]='1' and ParentModuleID='OP01'";

                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }


        public static DataTable SearchData(string sKeyWords, string sModulePrimaryID, string sPageType)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();


                    string strcmd = "";
                    string strcmd1 = @"SELECT ContentPublishId , '1' as pageType,Title,Content 
FROM CS_ContentPublish WHERE ContentPublishId IN
(SELECT ContentPublishId from dbo.CS_ContentPublish where contains (title,@sKeyWords)
UNION 
SELECT ContentPublishId FROM dbo.CS_ContentPublish WHERE CONTAINS(CONTENT,@sKeyWords)
) and [Status]='1'
";

                    string strcmd2 = @"SELECT ContentPublishId , '2' as pageType,Title,Content 
FROM KS_ContentPublish WHERE ContentPublishId IN
(SELECT ContentPublishId from dbo.KS_ContentPublish where contains (title,@sKeyWords)
UNION 
SELECT ContentPublishId FROM dbo.KS_ContentPublish WHERE CONTAINS(CONTENT,@sKeyWords)
) and [Status]='1'
";

                    string strcmd3 = @"SELECT PictureNewsId as ContentPublishId, '3' as pageType,Title,Content  
FROM FP_PictureNews WHERE PictureNewsId IN(
SELECT PictureNewsId from dbo.FP_PictureNews where contains (title,@sKeyWords)
UNION 
SELECT PictureNewsId FROM dbo.FP_PictureNews WHERE CONTAINS(CONTENT,@sKeyWords))
and [Status]='1'
";

                    if (sPageType.Trim()=="0")
                    {
                        strcmd = strcmd1 + " UNION  all " + strcmd2 + " UNION  all " + strcmd3;
                    }
                    if (sPageType.Trim() == "1")
                    {
                        strcmd = strcmd1;
                        if (!string.IsNullOrEmpty(sModulePrimaryID))
                        {
                            strcmd += " and ContentTypeId in (select ContentTypeId from dbo.SYS_ContentType where ModulePrimaryID =@ModulePrimaryID)  ";
                            dpc.AddWithValue("@ModulePrimaryID", sModulePrimaryID);
                        }
                    }
                    if (sPageType.Trim() == "2")
                    {
                        strcmd = strcmd2;
                        if (!string.IsNullOrEmpty(sModulePrimaryID))
                        {
                            strcmd += " and ContentTypeId in (select ContentTypeId from dbo.SYS_ContentType where ModulePrimaryID =@ModulePrimaryID) ";
                            dpc.AddWithValue("@ModulePrimaryID", sModulePrimaryID);
                        }
                    }
                    if (sPageType.Trim() == "3")
                    {
                        strcmd = strcmd3;
                    }
                    dpc.AddWithValue("@sKeyWords", sKeyWords);
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable GetHomeTopModuleList()
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " select " + SysModuleTable.C_MODULEID;
            sSql += "," + SysModuleTable.C_MODULENAME + "," + SysModuleTable.C_PAGETYPE + "," + SysModuleTable.C_ORDERNO + "," + SysModuleTable.C_MODULEPRIMARYID + "," + SysModuleTable.C_FUNCTIONURL + "," + SysModuleTable.C_PARENTMODULEID; ;
            sSql += " from " + SysModuleTable.C_TABLENAME;
            sSql += " where " + SysModuleTable.C_PAGETYPE + "='" + BaseEnum.PageType.F.ToString() + "'";
            sSql += " and  " + SysModuleTable.C_STATUS + "= '1' ";
            sSql += " order by " + SysModuleTable.C_MODULEPRIMARYID;

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
        }

        public static void UpdateUrl(DataAccessBroker broker)
        {
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            string strcmd = @"UPDATE SYS_Module SET FunctionUrl = UrlTep+'?modulemainid='+CONVERT(VARCHAR(50),ModulePrimaryID)
WHERE ModuleID LIKE 'OP01%' OR ModuleID LIKE 'CS01%'";
            broker.ExecuteSQL(strcmd, dpc);
        }

        public static DataTable SelectCheck(bool IsAll)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT A.* FROM SYS_Module A WHERE CHARINDEX('a',A.MODULERIGHTS)>0";
                    if (!IsAll)
                    {
                        strcmd += "AND NOT EXISTS(SELECT * FROM SYS_BillCheck AA WHERE AA.BillCode=A.ModuleID)";
                    }
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }

        public static DataTable SelectView(bool IsAll)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    string strcmd = @"SELECT A.* FROM SYS_Module A WHERE CHARINDEX('X',A.MODULERIGHTS)>0";
                    if (!IsAll)
                    {
                        strcmd += " AND NOT EXISTS(SELECT * FROM SYS_AmountView AA WHERE AA.BillCode=A.ModuleID)";
                    }
                    return broker.FillSQLDataSet(strcmd, dpc).Tables[0];
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }
    }
}
